package com.informet.ipd.modelo.dao;

/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */



import com.informet.ipd.modelo.vo.AvancesPorActividadVO;
import com.informet.ipd.modelo.vo.CrecerConDeporteVO;
import com.informet.ipd.modelo.vo.ProgEstrVO;
import com.informet.ipd.modelo.vo.TalentoVO;


import com.informet.ipd.modelo.vo.UbigeoVO;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author LAPTOP01
 */
public class TalentoAlumnoMonitoreoDao extends DBase{


  Connection conn = null;

    public TalentoAlumnoMonitoreoDao() {
        super();
    }



////// Ini agregado para las permanencias

      public void  setCausaRetiro(TalentoVO bean, ResultSet rs) throws SQLException{

           bean.setCodcausaRet(rs.getString("NUM_CODcausaret_PK"));
           bean.setDescCausaRet(rs.getString("tx_desccausaret"));

    }

    public List getListCausaRetiro(String id,String trimestre)  throws SQLException {
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TalentoVO> lista = new ArrayList<TalentoVO>(0);

        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();

                sql = "  select * from me_causarettal ";


            rs = stmt.executeQuery(sql);
            TalentoVO bean = null;
            while (rs.next()) {
                bean = new TalentoVO();
                setCausaRetiro(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }
//// ////
    public void  setListAlumno(TalentoVO bean, ResultSet rs) throws SQLException{

          bean.setNombre(rs.getString("tx_nombretal"));
          bean.setTxsDescDisc(rs.getString("tx_descdisc"));
          bean.setDescripcionAspectos(rs.getString("tip"))  ;
          bean.setCodAlumno(rs.getString("num_codalumtal_pk"));
          bean.setDescAspTecnico(rs.getString("tx_descasptec"));

          bean.setCodAspTecnico(rs.getString("num_codasptec_pk"));

          bean.setNumEntradaEt(rs.getString("num_entradaet"));
          bean.setTrimestre1(rs.getString("num_trim1cet"));
          bean.setTrimestre2(rs.getString("num_trim2cet"));
          bean.setCodCcd(Integer.parseInt(rs.getString("num_talento_pk")));


          bean.setTrimestre3(rs.getString("num_trim3cet"));
          bean.setTrimestre4(rs.getString("num_trim4cet"));

          bean.setAcumulado(rs.getString("acumulado"));

          bean.setCodcausaRet(rs.getString("num_codcausaret_pk"));
          bean.setDescPermanencia(rs.getString("num_permaneceet"));

          bean.setNumPuntuacionPersonal(rs.getString("NUM_RESULTADOPERSONAL"));



    }


public List getListAlumno(String direccion,String ugel,String anio,String codDisciplina,String profesor,String nivel,
                          String continuidad,String  institucionEducativa,String  supervisor,String  anioInicio) throws SQLException {

        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<TalentoVO> lista = new ArrayList<TalentoVO>(0);
        //lista=null;
        String sql = "";

        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();

                        sql="     select "+
                            "     a.tx_nombretal, d.tx_descdisc, "+
                            "     'Aspecto Tec' as tip, et.num_codalumtal_pk,  "+
                            "     at.tx_descasptec, et.num_codasptec_pk, "+
                            "     et.num_entradaet, isnull(et.num_trim1cet,0) as num_trim1cet,  "+
                            "     isnull(et.num_trim2cet,0) as num_trim2cet, et.num_talento_pk, "+

                            "     isnull(et.num_trim3cet,0) as num_trim3cet,"+
                            "     isnull(et.num_trim4cet,0) as num_trim4cet, "+

                            " (isnull(et.num_trim1cet,0)+isnull(et.num_trim2cet,0)+isnull(et.num_trim3cet,0)+isnull(et.num_trim4cet,0)) as acumulado "+



                            " ,isnull(et.num_codcausaret_pk,0) as num_codcausaret_pk "+
                            " ,isnull(et.num_permaneceet,'') as num_permaneceet, "+


                            " isnull(et.NUM_RESULTADOPERSONAL,'') as NUM_RESULTADOPERSONAL "+

                            "  from me_evatectal et inner join me_alumnostal a on et.num_codalumtal_pk=a.num_codalumtal_pk "+
                            "                               and et.num_talento_pk=a.num_talento_pk "+
                            "                    inner join me_asptecnicostal at on at.num_codasptec_pk=et.num_codasptec_pk "+
                            "                    inner join me_disciplinatal d on at.num_coddisc_pk=d.num_coddisc_pk "+
                            "                    inner join me_talent c on c.num_talento_pk=a.num_talento_pk "+

               //             "                    inner join me_causaret cr on cr.num_codcausaret_pk=et.num_codcausaret_pk "+

                            "  where 1=1  ";

                       // if(ugel!=null)
                       //         if(!ugel.equals("-1"))
                       //     sql=sql+" and c.num_codugel_pk="+ugel;

                        if(anio!=null)
                                if(!anio.equals("-1"))
                            sql=sql+" and c.num_aniotal= "+anio;

                        if(direccion!=null)
                                if(!direccion.equals(""))
                            sql=sql+" and c.tx_ubicatal like '%"+direccion+"%'";

                      //  if(institucionEducativa!=null)
                      //          if(!institucionEducativa.equals(""))
                     //       sql=sql+" and c.tx_instccd like '%"+institucionEducativa+"%'";

                        if(profesor!=null)
                                if(!profesor.equals(""))
                            sql=sql+" and c.tx_proftal  like '%"+profesor+"%'";

                       if(supervisor!=null)
                                if(!supervisor.equals(""))
                            sql=sql+" and c.tx_supertal  like '%"+supervisor+"%'";

                       if(anioInicio!=null)
                                if(!anioInicio.equals("-1"))
                            sql=sql+" and c.num_anioinital="+anioInicio;

                       if(codDisciplina!=null)
                                if(!codDisciplina.equals("-1"))
                            sql=sql+" and c.num_coddisc_pk="+codDisciplina;

                       if(nivel!=null)
                                if(!nivel.equals("-1"))
                            sql=sql+" and c.num_niveltal="+nivel;

                       if(continuidad!=null)
                                if(!continuidad.equals("-1"))
                            sql=sql+" and c.num_continuidad="+continuidad;

                         sql=sql+" union " +

                           " select  "+
                           "          a.tx_nombretal, 'No tiene Disciplina',  "+
                           "          'Aspecto Fis' as tip, ef.num_codalumtal_pk, "+
                           "          af.tx_descaspfis, ef.num_codaspfis_pf, "+
                           "          ef.num_entradaef, isnull(ef.num_trim1cef,0)as num_trim1cef,  "+
                           "          isnull(ef.num_trim2cef,0)as num_trim2cef ,ef.num_talento_pk, " +

                           "     isnull(ef.num_trim3cef,0) as num_trim3cef,"+
                           "     isnull(ef.num_trim4cef,0) as num_trim4cef, "+


                           "(isnull(ef.num_trim1cef,0)+isnull(ef.num_trim2cef,0)+isnull(ef.num_trim3cef,0)+isnull(ef.num_trim4cef,0)) as acumulado"+

                           " ,0	"+
                           " , isnull(ef.num_permaneceef,''), "+

                           " isnull(ef.NUM_RESULTADOPERSONAL,'') as NUM_RESULTADOPERSONAL "+


                           " from me_evafistal ef inner join me_alumnostal a on ef.num_codalumtal_pk=a.num_codalumtal_pk "+
                           "                              and ef.num_talento_pk =a.num_talento_pk " +
                           "                   inner join me_aspfisicostal af on af.num_codaspfis_pf=ef.num_codaspfis_pf " +
                           "                   inner join me_talent c on c.num_talento_pk=a.num_talento_pk " +
                           " where 1=1  " ;

//                        if(ugel!=null)
//                                if(!ugel.equals("-1"))/
//                            sql=sql+" and c.num_codugel_pk="+ugel;

                        if(anio!=null)
                                if(!anio.equals("-1"))
                            sql=sql+" and c.num_anioccd= "+anio;

                        if(direccion!=null)
                                if(!direccion.equals(""))
                            sql=sql+" and c.tx_ubicatal like '%"+direccion+"%'";

                        /*
                         if(institucionEducativa!=null)
                                if(!institucionEducativa.equals(""))
                            sql=sql+" and c.tx_instccd like '%"+institucionEducativa+"%'";
                        */

                        if(profesor!=null)
                                if(!profesor.equals(""))
                            sql=sql+" and c.tx_proftal like '%"+profesor+"%'";

                       if(supervisor!=null)
                                if(!supervisor.equals(""))
                            sql=sql+" and c.tx_supertal like '%"+supervisor+"%'";

                       if(anioInicio!=null)
                                if(!anioInicio.equals("-1"))
                            sql=sql+" and c.num_anioinital="+anioInicio;

                       if(codDisciplina!=null)
                                if(!codDisciplina.equals("-1"))
                            sql=sql+" and c.num_coddisc_pk="+codDisciplina;

                       if(nivel!=null)
                                if(!nivel.equals("-1"))
                            sql=sql+" and c.num_niveltal="+nivel;

                       if(continuidad!=null)
                                if(!continuidad.equals("-1"))
                            sql=sql+" and c.num_continuidad="+continuidad;


                        sql=sql+"  order by num_codalumtal_pk,tip desc     " ;




            rs = stmt.executeQuery(sql);
            TalentoVO bean1 = null;
            while (rs.next()) {
                bean1 = new TalentoVO();
                setListAlumno(bean1, rs);
                lista.add(bean1);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
      // JOptionPane.showMessageDialog(null, lista.size());
        return lista;
    }


      public String spMan(TalentoVO  bean) {
        String msj = "";
        //String codP=bean.getCodPrograma();
        try {

              /*@codAlumno int,@codCcd int,	@codAspTec int,	@tri1 int,@tri2 int,
           @tri3 int,	@tri4 int,@permanece varchar(100),@codCausa int,@tipoAspecto varchar(30)
           */

            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call Man_update_TalentoAspectosTecnicosFisicos(?,?,?,?,?,?,?,?,?,?)}");
            //codActividad, codAnio, titu_programa,justificacion,fechaInicio, fechaFin,monto

            cs.setString(1, bean.getCodAlumno());
            cs.setInt(2, bean.getCodCcd());
            cs.setString(3, bean.getCodAspTecnico());
            cs.setString(4, bean.getTrimestre1());
            cs.setString(5, bean.getTrimestre2());
            cs.setString(6, bean.getTrimestre3());
            cs.setString(7, bean.getTrimestre4());
            cs.setString(8, bean.getDescPermanencia());
            cs.setString(9, bean.getCodcausaRet());
            cs.setString(10, bean.getDescripcionAspectos());


            cs.execute();
        } catch (Exception e) {
            JOptionPane.showMessageDialog(null, e.toString());
            msj = e.toString();
            return msj;
        }
        return msj;
    }

}
